package com.dbflow5.sql.language;

import com.dbflow5.BaseUnitTest;
import com.dbflow5.config.DBFlowDatabase;
import com.dbflow5.config.FlowManager;
import com.dbflow5.models.SimpleTestModels;
import com.dbflow5.query.Method;
import com.dbflow5.query.NameAlias;
import com.dbflow5.query.Operator;
import com.dbflow5.query.OrderBy;
import com.dbflow5.query.SQLite;
import com.dbflow5.query.Where;
import com.dbflow5.query.property.PropertyFactory;

import static com.dbflow5.models.SimpleModel_Table.name;
import static com.dbflow5.models.TwoColumnModel_Table.id;

import org.junit.Test;

import java.util.ArrayList;
import java.util.function.Function;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotSame;
import static org.junit.Assert.fail;

import com.dbflow5.models.SimpleTestModels.SimpleModel;

public class WhereTest extends BaseUnitTest {
    @Test
    public void validateBasicWhere() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name"));
        assertEquals("SELECT * FROM SimpleModel WHERE name='name'", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateComplexQueryWhere() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).or(id.eq(1)).and(id.is(0).or(name.eq("hi")));
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' OR id=1 AND (id=0 OR name='hi')", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateGroupBy() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).groupBy(name);
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' GROUP BY name", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateGroupByNameAlias() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).groupBy(NameAlias.of("name"), NameAlias.of("id"));
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' GROUP BY name,id", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateGroupByNameProps() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).groupBy(name, id);
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' GROUP BY name,id", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateHaving() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).having(name.like("That"));
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' HAVING name LIKE 'That'", query.getQuery().trim());
        assertCanCopyQuery(query);
        assertEquals("SELECT * FROM SimpleModel GROUP BY exampleValue HAVING MIN(ROWID)>5", SQLite.select().from(SimpleModel.class).groupBy(NameAlias.rawBuilder("exampleValue").build())
            .having(Method.min(PropertyFactory.from(NameAlias.rawBuilder("ROWID").build())).greaterThan(5)).getQuery().trim());
    }

    @Test
    public void validateLimit() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).limit(10);
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' LIMIT 10", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateOffset() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.is("name")).offset(10);
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' OFFSET 10", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateWhereExists() {
//        val query = (select from SimpleModel::class
//        whereExists (select(name) from SimpleModel::class where name.like("Andrew")))
//        ("SELECT * FROM `SimpleModel` " +
//                "WHERE EXISTS (SELECT `name` FROM `SimpleModel` WHERE `name` LIKE 'Andrew')").assertEquals(query)

        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).whereExists(
                (SQLite.select(name).from(SimpleModel.class).where(name.like("Andrew")))
        );
        assertEquals("SELECT * FROM SimpleModel " +
            "WHERE EXISTS (SELECT name FROM SimpleModel WHERE name LIKE 'Andrew')", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateOrderByWhere() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.eq("name")).orderBy(name,true);
        assertEquals("SELECT * FROM SimpleModel WHERE name='name' ORDER BY name ASC", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateOrderByWhereAlias() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.eq("name")).orderBy(NameAlias.of("name"),true);
        assertEquals("SELECT * FROM SimpleModel " +
            "WHERE name='name' ORDER BY name ASC", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateOrderBy() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(name.eq("name"))
            .orderBy(OrderBy.fromNameAlias(NameAlias.of("name"),true).ascending());
        assertEquals("SELECT * FROM SimpleModel " +
            "WHERE name='name' ORDER BY name ASC", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    public <T> void assertCanCopyQuery(Where<T> query) {
        Where<T> actual = query.cloneSelf();
        assertEquals(query.getQuery(), actual.getQuery());
        assertNotSame(actual, query);
    }

    @Test
    public void validateOrderByAll() {
        Where<SimpleTestModels.TwoColumnModel> query = SQLite.select().from(SimpleTestModels.TwoColumnModel.class).where(name.eq("name"))
            .orderByAll(new ArrayList<OrderBy>(){{
                add(OrderBy.fromNameAlias(NameAlias.of("name"), true).ascending());
                add(OrderBy.fromNameAlias(NameAlias.of("id"), true).descending());
            }});
        assertEquals("SELECT * FROM TwoColumnModel " +
            "WHERE name='name' ORDER BY name ASC,id DESC", query.getQuery().trim());
        assertCanCopyQuery(query);
    }

    @Test
    public void validateNonSelectThrowError() {
        FlowManager.databaseForTable(SimpleModel.class, new Function<DBFlowDatabase, Void>() {
            @Override
            public Void apply(DBFlowDatabase db) {
                try {
                    SQLite.update(SimpleModel.class).set(name.is("name")).queryList(db);
                    fail("Non select passed");
                } catch (IllegalArgumentException i) {
                    // expected
                }

                try {
                    SQLite.update(SimpleModel.class).set(name.is("name")).queryList(db);
                    fail("Non select passed");
                } catch (IllegalArgumentException i) {
                    // expected
                }
                return null;
            }
        });
    }

    @Test
    public void validate_match_operator() {
        Where<SimpleModel> query = SQLite.select().from(SimpleModel.class).where(Operator.op("name").match("%s"));
        assertEquals("SELECT * FROM SimpleModel WHERE name MATCH '%s'", query.getQuery().trim());
        assertCanCopyQuery(query);
    }
}
